package com.szholly.pro.watergas_server.common.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;

@SuppressWarnings("deprecation")
public class ExportExcelUtils { 
		    private HSSFWorkbook wb = null; 
		    private HSSFSheet sheet = null; 
		    /**
		     * @param wb
		     * @param sheet
		     */ 
		    public ExportExcelUtils(HSSFWorkbook wb, HSSFSheet sheet) { 
		        super(); 
		        this.wb = wb; 
		        this.sheet = sheet; 
		    } 
		    /**
		     * @return the sheet
		     */ 
		    public HSSFSheet getSheet() { 
		        return sheet; 
		    } 
		    /**
		     * @param sheet
		     * the sheet to set
		     */ 
		    public void setSheet(HSSFSheet sheet) { 
		        this.sheet = sheet; 
		    } 
		    /**
		     * @return the wb
		     */ 
		    public HSSFWorkbook getWb() { 
		        return wb; 
		    } 
		    /**
		     * @param wb
		     * the wb to set
		     */ 
		    public void setWb(HSSFWorkbook wb) { 
		        this.wb = wb; 
		    } 
		    /**
		     * 创建通用EXCEL头部
		     * 
		     * @param headString
		     *            头部显示的字符
		     * @param colSum
		     *            该报表的列数
		     */ 
		    public void createNormalHead(String headString, int colSum) { 
		        HSSFRow row = sheet.createRow(0); 
		   
		        // 设置第一行 
		        HSSFCell cell = row.createCell(0); 
		        row.setHeight((short) 400); 
		        // 定义单元格为字符串类型 
		        cell.setCellType(HSSFCell.ENCODING_UTF_16); 
		        cell.setCellValue(new HSSFRichTextString(headString)); 
		        // 指定合并区域 
		        sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum)); 
		        HSSFCellStyle cellStyle = wb.createCellStyle(); 
		        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 
		        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 
		        cellStyle.setWrapText(true);// 指定单元格自动换行 
		        // 设置单元格字体 
		        HSSFFont font = wb.createFont(); 
		        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
		        font.setFontName("宋体"); 
		        font.setFontHeight((short) 300); 
		        cellStyle.setFont(font); 
		        cell.setCellStyle(cellStyle); 
		    } 
		    /**
		     * 创建通用报表第二行
		     * 
		     * @param params
		     *            统计条件数组
		     * @param colSum
		     *            需要合并到的列索引
		     */ 
		    public void createNormalTwoRow(String[] params, int colSum) { 
		        HSSFRow row1 = sheet.createRow(1); 
		        row1.setHeight((short) 300); 
		        HSSFCell cell2 = row1.createCell(0); 
		        cell2.setCellType(HSSFCell.ENCODING_UTF_16); 
		        cell2.setCellValue(new HSSFRichTextString("统计时间：" + params[0] + "至" 
		                + params[1])); 
		        // 指定合并区域  ALIGN_CENTER
		        sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) colSum)); 
		        HSSFCellStyle cellStyle = wb.createCellStyle(); 
		        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 
		        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 
		        cellStyle.setWrapText(true);// 指定单元格自动换行 
		        // 设置单元格字体 
		        HSSFFont font = wb.createFont(); 
		        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
		        font.setFontName("宋体"); 
		        font.setFontHeight((short) 250); 
		        cellStyle.setFont(font); 
		        cell2.setCellStyle(cellStyle); 
		    } 
		    /**
		     * 设置报表标题
		     * 
		     * @param columHeader
		     *            标题字符串数组
		     */ 
		    public void createColumHeader(String[] columHeader) { 
		        // 设置列头 
		        HSSFRow row2 = sheet.createRow(2); 

		        // 指定行高 
		        row2.setHeight((short) 600); 
		        HSSFCellStyle cellStyle = wb.createCellStyle(); 
		        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 
		        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 
		        cellStyle.setWrapText(true);// 指定单元格自动换行 
		        // 单元格字体 
		        HSSFFont font = wb.createFont(); 
		        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
		        font.setFontName("宋体"); 
		        font.setFontHeight((short) 250); 
		        cellStyle.setFont(font); 
		        /*
		169
		         * cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体
		170
		         * cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
		171
		         * cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		172
		         * cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		173
		         * cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		174
		         * cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		175
		         * cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		176
		         * cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		177
		         */ 
		        // 设置单元格背景色 
		        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); 
		        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
		        HSSFCell cell3 = null; 
		        for (int i = 0; i < columHeader.length; i++) { 
		            cell3 = row2.createCell(i); 
		            cell3.setCellType(HSSFCell.ENCODING_UTF_16); 
		            cell3.setCellStyle(cellStyle); 
		            cell3.setCellValue(new HSSFRichTextString(columHeader[i])); 
		        } 
		    } 
 
		    /**
		     * 输入EXCEL文件
		     * 
		     * @param fileName
		     *            文件名
		     */ 
		    public void outputExcel(String fileName) { 
		        FileOutputStream fos = null; 
		        try { 
		            fos = new FileOutputStream(new File(fileName)); 
		            wb.write(fos); 
		            fos.flush(); 
		            fos.close(); 
		        } catch (FileNotFoundException e) { 
		            e.printStackTrace(); 
		        } catch (IOException e) { 
		            e.printStackTrace(); 
		        } 
		    }
		    
			
			public HSSFCellStyle createCellStyle(HSSFWorkbook wb) {
		        // 设置这些样式  
				HSSFCellStyle style = wb.createCellStyle();  
		        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
		        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
		        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
		        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
		        // 指定当单元格内容显示不下时自动换行  
		        style.setWrapText(true); 
		        return style;
			}
			
			public HSSFCellStyle cellCenterStyle(HSSFWorkbook wb) {
		        // 设置这些样式  
				HSSFCellStyle style = wb.createCellStyle();    
		        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中  
		        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 
		        // 指定当单元格内容显示不下时自动换行  
		        style.setWrapText(true); 
		        return style;
			}
		    
		    
	}
